﻿using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;
using System;
using System.Collections;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;

namespace ExcelImport
{
    /// <summary>
    /// Excel 导入导出 接口
    /// </summary>
    /// <typeparam name="T">此接口操作的数据类型</typeparam>
    public interface IExcelImport<T> where T : class
    {
        /// <summary>
        /// 导入数据
        /// </summary>
        /// <param name="path">指定路径</param>
        /// <returns></returns>
        IEnumerable<T> Import(string path);

        /// <summary>
        /// 导出数据
        /// </summary>
        /// <param name="path">指定路径</param>
        /// <param name="datas">数据集合</param>
        /// <returns></returns>
        bool ExportData(string path, IEnumerable<T> datas);

        /// <summary>
        /// 导出模板
        /// </summary>
        /// <param name="path">指定路径</param>
        /// <returns></returns>
        bool ExportTemplate(string path);

    }

    /// <summary>
    /// excel 导入导出的基类
    /// </summary>
    /// <remarks>
    /// 抽象类，不能实例化
    /// </remarks>
    /// <typeparam name="T">可操作的数据类型</typeparam>
    public abstract class BaseExcelImport<T> : IExcelImport<T> where T : class
    {
        /// <summary>
        /// 正在操作的工作簿
        /// </summary>
        protected IWorkbook _workbook;

        /// <summary>
        /// 正在操作的行
        /// </summary>
        protected IRow _row;

        /// <summary>
        /// 正在操作的工作表
        /// </summary>
        protected ISheet _sheet;

        /// <summary>
        /// 表格名称
        /// </summary>
        /// <remarks>
        /// 
        /// </remarks>
        public string SheetName { get; private set; }

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="sheetName">表格名字</param>
        public BaseExcelImport(string sheetName = null)
        {
            if (string.IsNullOrWhiteSpace(sheetName))
            {
                SheetName = typeof(T).GetCustomAttributes(typeof(ExcelSheetAttribute), false).FirstOrDefault() is ExcelSheetAttribute attr && !string.IsNullOrWhiteSpace(attr.SheetName) ? attr.SheetName : typeof(T).Name;
            }
            else
            {
                SheetName = sheetName;
            }
        }

        /// <summary>
        /// 导出数据至指定文件
        /// </summary>
        /// <remarks>
        /// 
        /// </remarks>
        /// <param name="path">导出的文件路径</param>
        /// <param name="datas">需要导出的数据</param>
        /// <returns></returns>
        public abstract bool ExportData(string path, IEnumerable<T> datas);

        /// <summary>
        /// 导出数据模板
        /// </summary>
        /// <remarks>
        /// excel 文件只包括数据头部
        /// </remarks>
        /// <param name="path">导出的文件</param>
        /// <returns></returns>
        public abstract bool ExportTemplate(string path);

        /// <summary>
        /// 导入指定文件，获取数据集合
        /// </summary>
        /// <param name="path">导入文件路径</param>
        /// <returns>返回数据集合</returns>
        public abstract IEnumerable<T> Import(string path);

        /// <summary>
        /// 通过后缀名获取 Workbook
        /// </summary>
        /// <returns>获取的工作簿</returns>
        protected IWorkbook GetWorkbookBySuffix(FileStream fs, bool xlsxFile)
        {
            if (xlsxFile)
            {
                return fs == null ? new XSSFWorkbook() : new XSSFWorkbook(fs);
            }
            else
            {
                return fs == null ? new HSSFWorkbook() : new HSSFWorkbook(fs);
            }
        }

        /// <summary>
        /// 单元格值转换成字符串
        /// </summary>
        /// <remarks>
        /// 通过单元格的类型去执行转换
        /// </remarks>
        /// <param name="cell">指定的单元格</param>
        /// <returns></returns>
        protected object CellValueConvert2String(ICell cell)
        {
            object result;
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    result = cell.NumericCellValue.ToString();
                    break;
                default:
                    result = cell.StringCellValue;
                    break;
            }
            return result;
        }

        /// <summary>
        /// 获取单元格的值
        /// </summary>
        /// <param name="cell">指定的单元格</param>
        /// <returns></returns>
        protected virtual object GetCellValue(ICell cell)
        {
            object result;
            switch (cell.CellType)
            {
                case CellType.Numeric:
                    result = cell.NumericCellValue;
                    break;
                default:
                    result = cell.StringCellValue;
                    break;
            }
            return result;
        }


        /// <summary>
        /// 获取批注
        /// </summary>
        /// <param name="sheet">工作表</param>
        /// <param name="col2">列的位置</param>
        /// <param name="row2">行的位置</param>
        /// <param name="content">内容</param>
        /// <param name="author">作者</param>
        /// <returns></returns>
        protected IComment GetCellComment(ISheet sheet, int col2, int row2, string content, string author)
        {
            IDrawing drawing = sheet.CreateDrawingPatriarch();
            IClientAnchor anchor = drawing.CreateAnchor(0, 0, 0, 0, 0, 0, col2, row2);
            anchor.AnchorType = AnchorType.DontMoveAndResize;
            IComment comment = drawing.CreateCellComment(anchor);
            comment.String = sheet is HSSFSheet ? new HSSFRichTextString(content) : new XSSFRichTextString(content) as IRichTextString;
            comment.Author = author;
            return comment;
        }

        /// <summary>
        /// 设置数据有效性
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="firstRow"></param>
        /// <param name="lastRow"></param>
        /// <param name="firstCol"></param>
        /// <param name="lastCol"></param>
        /// <param name="explictList"></param>
        protected void SetDataValidation(ISheet sheet, int firstRow, int lastRow, int firstCol, int lastCol, string[] explictList)
        {
            //设置限制的区域
            CellRangeAddressList regions = new CellRangeAddressList(firstRow, lastRow, firstCol, lastCol);
            //设置限制文字
            DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(explictList);
            //区域与文字设置数据有效性对象
            IDataValidation validation = new HSSFDataValidation(regions, constraint);
            //在指定列表中加入该限制
            sheet.AddValidationData(validation);
        }

        /// <summary>
        /// 检查导出文件路径是否可用
        /// </summary>
        /// <param name="filePath">文件的路径</param>
        /// <returns>存在返回 <c>true</c></returns>
        protected bool CheckExportFilePath(string filePath)
        {
            bool result = true;
            if (string.IsNullOrWhiteSpace(filePath))
            {
                result = false;
            }
            return result;
        }

        /// <summary>
        /// 获取单元格样式
        /// </summary>
        /// <param name="workbook">指定的工作簿</param>
        /// <param name="type">指定的样式 目前只有 <c>Header</c></param>
        /// <returns></returns>
        protected ICellStyle GetCellStyle(IWorkbook workbook, string type)
        {
            ICellStyle cellStyle = workbook.CreateCellStyle();
            IFont font = workbook.CreateFont();
            switch (type)
            {
                case "Header":
                    font.Boldweight = (short)FontBoldWeight.Bold;
                    cellStyle.SetFont(font);
                    cellStyle.Alignment = HorizontalAlignment.Center;
                    cellStyle.VerticalAlignment = VerticalAlignment.Center;

                    break;
                default:
                    break;
            }
            return cellStyle;
        }

    }

}
